package com.examsys.dao;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.Admin;
import com.examsys.po.Paper;
import com.examsys.po.PaperDetail;
import com.examsys.po.PaperSection;
import com.examsys.po.Question;
import com.examsys.po.QuestionDb;
import com.examsys.po.QuestionOptions;

/**
 * 试题数据访问层实现类
 * @author edu-1
 *
 */
public class QuestionDaoImpl extends AbstractBaseDao<Question, Integer> implements QuestionDao {

	/**
	 * 添加试题
	 */
	@Override
	public void add(Question obj) throws Exception {
		//构造插入语句
		String sql="Insert into QUESTION (ID,DB_ID,ADMIN_ID,QTYPE,QLEVEL,QFROM,CONTENT,SKEY,KEY_DESC,CREATE_DATE,STATUS,REMARK) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getId(),obj.getQuestionDb().getId(),obj.getAdmin().getId(),obj.getQtype(),obj.getQlevel(),obj.getQfrom(),
				obj.getContent(),obj.getSkey(),obj.getKey_desc(),obj.getCreate_date(),obj.getStatus(),obj.getRemark()});
	}

	/**
	 * 更新试题
	 */
	@Override
	public void update(Question obj) throws Exception {
		//构造更新语句
		String sql="UPDATE QUESTION SET DB_ID=?,ADMIN_ID=?,QTYPE=?,QLEVEL=?,QFROM=?,CONTENT=?,SKEY=?,KEY_DESC=?,CREATE_DATE=?,STATUS=?,REMARK=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getQuestionDb().getId(),obj.getAdmin().getId(),obj.getQtype(),obj.getQlevel(),obj.getQfrom(),
				obj.getContent(),obj.getSkey(),obj.getKey_desc(),obj.getCreate_date(),obj.getStatus(),obj.getRemark(),obj.getId()});
	}

	/**
	 * 删除试题
	 * @param id 编号
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM QUESTION WHERE ID=?";
		this.execute(sql, new Object[]{id});
	}

	/**
	 * 获取试题
	 * @param id 编号
	 */
	@Override
	public Question get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.DB_ID,a.ADMIN_ID,a.QTYPE,a.QLEVEL,a.QFROM,a.CONTENT,a.SKEY,a.KEY_DESC,a.CREATE_DATE,a.STATUS,a.REMARK,b.NAME DB_NAME,c.USER_NAME FROM QUESTION a LEFT JOIN QUESTION_DB b ON a.DB_ID=b.ID LEFT JOIN ADMIN c ON a.ADMIN_ID=c.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		Integer qtype = (Integer)m.get("QTYPE");//代码
		Integer qlevel = (Integer)m.get("QLEVEL");//信息内容
		String qfrom = (String)m.get("QFROM");//题库名称
		String content = (String)m.get("CONTENT");//题库名称
		String skey = (String)m.get("SKEY");//题库名称
		String key_desc = (String)m.get("KEY_DESC");//题库名称
		String status = (String)m.get("STATUS");//题库名称
		String remark = (String)m.get("REMARK");//题库名称
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
		Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
		
		Question question=new Question();//创建实体类对象
		question.setId(idd);
		question.setQtype(qtype);
		question.setQlevel(qlevel);
		question.setQfrom(qfrom);
		question.setContent(content);
		question.setSkey(skey);
		question.setKey_desc(key_desc);
		question.setStatus(status);
		question.setRemark(remark);
		question.setCreate_date(create_date2);
		
		Integer db_id = (Integer)m.get("DB_ID");//题库编号
		String db_name = (String)m.get("DB_NAME");//题库名称
		
		QuestionDb questionDb=new QuestionDb();//创建实体类对象
		questionDb.setId(db_id);
		questionDb.setName(db_name);
		
		Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
		String user_name = (String)m.get("USER_NAME");//管理员名称
		
		Admin admin=new Admin();//创建实体类对象
		admin.setId(admin_id);
		admin.setUser_name(user_name);
		
		question.setAdmin(admin);//设置关联对象
		question.setQuestionDb(questionDb);//设置关联对象
		
		//构建查询语句
		sql="SELECT a.ID,a.QUESTION_ID,a.SALISA,a.SOPTION,a.SEXTEND FROM QUESTION_OPTIONS a LEFT JOIN QUESTION b ON a.QUESTION_ID=b.ID WHERE a.QUESTION_ID="+id;
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<QuestionOptions> list=new ArrayList<QuestionOptions>();//存放QuestionOptions类型对象的集合
		
		for(Map m2:resultList){
			Integer odd = (Integer)m2.get("ID");//编号
			Integer question_id = (Integer)m2.get("QUESTION_ID");//题目编号
			String salisa = (String)m2.get("SALISA");//选项编号
			String soption = (String)m2.get("SOPTION");//选项描述
			String sextend = (String)m2.get("SEXTEND");//面试题的扩展项
			
			QuestionOptions questionOptions=new QuestionOptions();//创建实体类对象
			questionOptions.setId(odd);
			questionOptions.setSalisa(salisa);
			questionOptions.setSoption(soption);
			questionOptions.setSextend(sextend);
			
			questionOptions.setQuestion(question);//设置关联对象
			
			list.add(questionOptions);
		}
		
		question.setQuestionOptionss(list);
		
		return question;
	}

	/**
	 * 获取所有试题
	 * 
	 */
	@Override
	public List<Question> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.DB_ID,a.ADMIN_ID,a.QTYPE,a.QLEVEL,a.QFROM,a.CONTENT,a.SKEY,a.KEY_DESC,a.CREATE_DATE,a.STATUS,a.REMARK,b.NAME DB_NAME,c.USER_NAME FROM QUESTION a LEFT JOIN QUESTION_DB b ON a.DB_ID=b.ID LEFT JOIN ADMIN c ON a.ADMIN_ID=c.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Question> list =new ArrayList<Question>();//存放Question类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer qtype = (Integer)m.get("QTYPE");//代码
			Integer qlevel = (Integer)m.get("QLEVEL");//信息内容
			String qfrom = (String)m.get("QFROM");//题库名称
			String content = (String)m.get("CONTENT");//题库名称
			String skey = (String)m.get("SKEY");//题库名称
			String key_desc = (String)m.get("KEY_DESC");//题库名称
			String status = (String)m.get("STATUS");//题库名称
			String remark = (String)m.get("REMARK");//题库名称
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
			Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
			
			Question question=new Question();//创建实体类对象
			question.setId(idd);
			question.setQtype(qtype);
			question.setQlevel(qlevel);
			question.setQfrom(qfrom);
			question.setContent(content);
			question.setSkey(skey);
			question.setKey_desc(key_desc);
			question.setStatus(status);
			question.setRemark(remark);
			question.setCreate_date(create_date2);
			
			Integer db_id = (Integer)m.get("DB_ID");//题库编号
			String db_name = (String)m.get("DB_NAME");//题库名称
			
			QuestionDb questionDb=new QuestionDb();//创建实体类对象
			questionDb.setId(db_id);
			questionDb.setName(db_name);
			
			Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员库编号
			String user_name = (String)m.get("USER_NAME");//管理员名称
			
			Admin admin=new Admin();//创建实体类对象
			admin.setId(admin_id);
			admin.setUser_name(user_name);
			
			question.setAdmin(admin);//设置关联对象
			question.setQuestionDb(questionDb);//设置关联对象
			list.add(question);
		}
		
		return list;
	}

	/**
	 * 带条件获取试题
	 * 
	 */
	@Override
	public List<Question> getList(Question obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.DB_ID,a.ADMIN_ID,a.QTYPE,a.QLEVEL,a.QFROM,a.CONTENT,a.SKEY,a.KEY_DESC,a.CREATE_DATE,a.STATUS,a.REMARK,b.NAME DB_NAME,c.USER_NAME FROM QUESTION a LEFT JOIN QUESTION_DB b ON a.DB_ID=b.ID LEFT JOIN ADMIN c ON a.ADMIN_ID=c.ID WHERE 1=1";
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getQuestionDb()!=null&&obj.getQuestionDb().getId()!=null
					&&obj.getQuestionDb().getId()!=0){
				sql+=" AND a.DB_ID="+obj.getQuestionDb().getId();
			}
			
			if(obj.getQtype()!=null&&obj.getQtype()!=0){
				sql+=" AND a.QTYPE="+obj.getQtype();
			}
			
			if(obj.getQlevel()!=null&&obj.getQlevel()!=0){
				sql+=" AND a.QLEVEL="+obj.getQlevel();
			}
			
			if(obj.getContent()!=null&&!"".equals(obj.getContent())){
				sql+=" AND a.CONTENT LIKE '%"+obj.getContent()+"%'";
			}
			
		}
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Question> list =new ArrayList<Question>();//存放Question类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer qtype = (Integer)m.get("QTYPE");//代码
			Integer qlevel = (Integer)m.get("QLEVEL");//信息内容
			String qfrom = (String)m.get("QFROM");//题库名称
			String content = (String)m.get("CONTENT");//题库名称
			String skey = (String)m.get("SKEY");//题库名称
			String key_desc = (String)m.get("KEY_DESC");//题库名称
			String status = (String)m.get("STATUS");//题库名称
			String remark = (String)m.get("REMARK");//题库名称
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
			Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
			
			Question question=new Question();//创建实体类对象
			question.setId(idd);
			question.setQtype(qtype);
			question.setQlevel(qlevel);
			question.setQfrom(qfrom);
			question.setContent(content);
			question.setSkey(skey);
			question.setKey_desc(key_desc);
			question.setStatus(status);
			question.setRemark(remark);
			question.setCreate_date(create_date2);
			
			Integer db_id = (Integer)m.get("DB_ID");//题库编号
			String db_name = (String)m.get("DB_NAME");//题库名称
			
			QuestionDb questionDb=new QuestionDb();//创建实体类对象
			questionDb.setId(db_id);
			questionDb.setName(db_name);
			
			Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
			String user_name = (String)m.get("USER_NAME");//管理员名称
			
			Admin admin=new Admin();//创建实体类对象
			admin.setId(admin_id);
			admin.setUser_name(user_name);
			
			question.setAdmin(admin);//设置关联对象
			question.setQuestionDb(questionDb);//设置关联对象
			list.add(question);
		}
		return list;
	}

	/**
	 * 通过条件获得信息列表，不包括给定编号的试题
	 * @param notInIds
	 * @return
	 */
	public List<Question> getList(String notInIds,Question question) throws Exception{
		//构建查询语句
		String sql="SELECT a.ID,a.DB_ID,a.ADMIN_ID,a.QTYPE,a.QLEVEL,a.QFROM,a.CONTENT,a.SKEY,a.KEY_DESC,a.CREATE_DATE,a.STATUS,a.REMARK,b.NAME DB_NAME,c.USER_NAME FROM QUESTION a LEFT JOIN QUESTION_DB b ON a.DB_ID=b.ID LEFT JOIN ADMIN c ON a.ADMIN_ID=c.ID WHERE 1=1";
		if(notInIds!=null&&!"".equals(notInIds)){//条件构造
			sql+=" AND a.ID NOT IN("+notInIds+")";
		}
		
		if(question.getQuestionDb()!=null&&question.getQuestionDb().getId()!=null
				&&question.getQuestionDb().getId()!=0){
			sql+=" AND a.DB_ID="+question.getQuestionDb().getId();
		}
		
		if(question.getQtype()!=null&&question.getQtype()!=0){
			sql+=" AND a.QTYPE="+question.getQtype();
		}
		
		if(question.getQlevel()!=null&&question.getQlevel()!=0){
			sql+=" AND a.QLEVEL="+question.getQlevel();
		}
		
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Question> list =new ArrayList<Question>();//存放Question类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer qtype = (Integer)m.get("QTYPE");//代码
			Integer qlevel = (Integer)m.get("QLEVEL");//信息内容
			String qfrom = (String)m.get("QFROM");//题库名称
			String content = (String)m.get("CONTENT");//题库名称
			String skey = (String)m.get("SKEY");//题库名称
			String key_desc = (String)m.get("KEY_DESC");//题库名称
			String status = (String)m.get("STATUS");//题库名称
			String remark = (String)m.get("REMARK");//题库名称
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
			Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
			
			Question question1=new Question();//创建实体类对象
			question1.setId(idd);
			question1.setQtype(qtype);
			question1.setQlevel(qlevel);
			question1.setQfrom(qfrom);
			question1.setContent(content);
			question1.setSkey(skey);
			question1.setKey_desc(key_desc);
			question1.setStatus(status);
			question1.setRemark(remark);
			question1.setCreate_date(create_date2);
			
			Integer db_id = (Integer)m.get("DB_ID");//题库编号
			String db_name = (String)m.get("DB_NAME");//题库名称
			
			QuestionDb questionDb=new QuestionDb();//创建实体类对象
			questionDb.setId(db_id);
			questionDb.setName(db_name);
			
			Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
			String user_name = (String)m.get("USER_NAME");//管理员名称
			
			Admin admin=new Admin();//创建实体类对象
			admin.setId(admin_id);
			admin.setUser_name(user_name);
			
			question1.setAdmin(admin);//设置关联对象
			question1.setQuestionDb(questionDb);//设置关联对象
			list.add(question1);
		}
		return list;
	}
	
	/**
	 * 获得系列值的方法
	 */
	@Override
	public Integer getSeq() throws Exception {
		String sql="select QUESTION_ID_SEQ.nextval SEQ from dual";
		Map map = this.uniqueQuery(sql, new Object[]{});
		Integer seq =(Integer) map.get("SEQ");
		return seq;
	}

}
